﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class DiscussThemeCommentstbDAO
    {
        /// <summary>
        /// 将主题评论保存到数据库中
        /// </summary>
        /// <param name="dtCContent"></param>
        /// <param name="dtCCreateTime"></param>
        /// <param name="dtCCUser"></param>
        /// <param name="dtId"></param>
        /// <returns></returns>
        public int SaveDtCommentsInfo(string dtCContent, DateTime dtCCreateTime, int dtCUser, int dtId)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into DiscussThemeComments values(@dtCContent, @dtCCreateTime, @dtCUser, @dtId)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@dtCContent", dtCContent));
                sqlCmd.Parameters.Add(new SqlParameter("@dtCCreateTime", dtCCreateTime));
                sqlCmd.Parameters.Add(new SqlParameter("@dtCUser", dtCUser));
                sqlCmd.Parameters.Add(new SqlParameter("@dtId", dtId));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据主题编号查找该主题的所有评论
        /// </summary>
        /// <param name="dtId"></param>
        /// <returns></returns>
        public List<DiscussThemeCommentstb> FindALLByDtId(int dtId)
        {
            List<DiscussThemeCommentstb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from DiscussThemeComments where DtId = @dtId";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@dtId", dtId));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<DiscussThemeCommentstb>();
                    UserDAO userDAO = new UserDAO();
                    DiscussThemetbDAO dtDAO = new DiscussThemetbDAO();
                    while (sqlDr.Read())
                    {
                        DiscussThemeCommentstb dtctb = new DiscussThemeCommentstb();
                        dtctb.DtCommentsId = (int)sqlDr["DtCommentsId"];
                        dtctb.DtCommentsContent = (string)sqlDr["DtCommentsContent"];
                        dtctb.DtCommentsCreateTime = (DateTime)sqlDr["DtCommentsCreateTime"];
                        dtctb.DtCommentsAuthor = userDAO.FindByUserId((int)sqlDr["DtCommentsAuthor"]);
                        dtctb.DtInfo = dtDAO.FindById((int)sqlDr["DtId"]);
                        list.Add(dtctb);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据主题编号查找该主题发表的最后评论信息
        /// </summary>
        /// <param name="dtId"></param>
        /// <param name="lastReadTime"></param>
        /// <returns></returns>
        public DiscussThemeCommentstb FindTheOneByLastReadTime(int dtId)
        {
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            DiscussThemeCommentstb dtctb = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from DiscussThemeComments where DtId = @dtId order by DtCommentsCreateTime desc";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@dtId", dtId));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    dtctb = new DiscussThemeCommentstb();
                    UserDAO userDAO = new UserDAO();
                    DiscussThemetbDAO dtDAO = new DiscussThemetbDAO();
                    sqlDr.Read();
                    dtctb.DtCommentsId = (int)sqlDr["DtCommentsId"];
                    dtctb.DtCommentsContent = (string)sqlDr["DtCommentsContent"];
                    dtctb.DtCommentsCreateTime = (DateTime)sqlDr["DtCommentsCreateTime"];
                    dtctb.DtCommentsAuthor = userDAO.FindByUserId((int)sqlDr["DtCommentsAuthor"]);
                    dtctb.DtInfo = dtDAO.FindById((int)sqlDr["DtId"]);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return dtctb;
        }
    }
}
